package com.hzlh.common.util;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

/**
 * excel操作
 * 
 * @author sgy
 * @date 2016/10/8
 */
public class ExcelUtil {

	private static Logger logger = Logger.getLogger(ExcelUtil.class);

	/**
	 * 获取excel中的内容
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "rawtypes" })
	public static List getExcelContext(MultipartFile file) throws Exception {
		try {
			String fileName = file.getOriginalFilename();
			if (fileName != null) {
				String postfix = fileName.substring(fileName.lastIndexOf("."), fileName.length()).toLowerCase();
				if (postfix.equals(".xls")) {
					return readXls(file.getInputStream());
				} else if (postfix.equals(".xlsx")) {
					return readXlsx(file.getInputStream());
				}
			}
		} catch (Exception e) {
			logger.error("---------------读取execl文件出错-------------");
			throw new Exception("读取execl文件出错"+e.getMessage());
		}
		return null;
	}

	/**
	 * Read the Excel Xlsx
	 * 
	 * @param is
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static List readXlsx(InputStream is) throws Exception {
		XSSFWorkbook xssfWorkbook = null;
		List list = null;
		String[] array = null;
		int rowNum;// 行数
		int columnNum;// 列数
		// Read the Sheet
		try {
			list = new ArrayList();
			xssfWorkbook = new XSSFWorkbook(is);
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if (xssfSheet == null) {
					continue;
				}
				rowNum = xssfSheet.getLastRowNum();
				// Read the Row
				for (int i = 1; i <= rowNum; i++) {
					XSSFRow xssfRow = xssfSheet.getRow(i);
					if (xssfRow != null) {
						columnNum = xssfRow.getLastCellNum();
						array = new String[columnNum];
						for (int j = 0; j < columnNum; j++) {
							array[j] = (xssfRow.getCell(j) == null) ? "" : xssfRow.getCell(j).toString();
						}
						list.add(array);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			if (is != null) {
				is.close();
			}
			if (xssfWorkbook != null) {
				xssfWorkbook.close();
			}
		}
		return list;
	}

	/**
	 * Read the Excel Xls
	 * 
	 * @param is
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List readXls(InputStream is) throws Exception {
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		List list = new ArrayList();
		String[] array = null;
		int rowNum;// 行数
		int columnNum;// 列数
		try {
			// Read the Sheet
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}
				rowNum = hssfSheet.getLastRowNum();
				// Read the Row
				for (int i = 1; i <= rowNum; i++) {
					HSSFRow hssfRow = hssfSheet.getRow(i);
					if (hssfRow != null) {
						columnNum = hssfRow.getLastCellNum();
						array = new String[columnNum];
						for (int j = 0; j < columnNum; j++) {
							array[j] = (hssfRow.getCell(j) == null) ? "" : hssfRow.getCell(j).toString();
						}
						list.add(array);
					}
				}
			}
		} catch (Exception e) {
			throw new Exception();
		} finally {
			if (is != null) {
				is.close();
			}
			if (hssfWorkbook != null) {
				hssfWorkbook.close();
			}
		}
		return list;
	}
}
